package report;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import connect.ConnectionHelper;
import entities.Report;
import entities.ReportDetail;
import flex.messaging.io.amf.ASObject;


public class ReportService 
{
	public String insert_Report(String rtpName, String rtpStatus, String rptFileName, String rptStoreName,
			String rptExport, String userAction,  Collection<ASObject> rptDetails) throws Exception
	{
		String strResult = "Error";  	
		String rptID =  "";
		int iCountItem = 0 ;
		Connection c = null;	
		try 
		{
			c = ConnectionHelper.getConnection();
			CallableStatement proc_stmt = null;
			proc_stmt = c.prepareCall("{ call report_insert(?,?,?,?,?,?,?,?) }");
			proc_stmt.setString(1, rtpName);
			proc_stmt.setString(2, rtpName);	         
			proc_stmt.setString(3, rtpStatus);
			proc_stmt.setString(4, userAction);	
			proc_stmt.setString(5, rptFileName);
			proc_stmt.setString(6, rptStoreName);	
			proc_stmt.setString(7, rptExport);
			proc_stmt.registerOutParameter(8, Types.VARCHAR);
			proc_stmt.executeQuery();                   
			rptID = proc_stmt.getString(8);
			
			if (rptDetails.size() > 0)
			{
				iCountItem = 1 ;
				for(ASObject row: rptDetails)
				{					
					CallableStatement proc_detail = null;
					proc_detail = c.prepareCall("{ call reportdetail_insert(?,?,?,?,?,?,?) }");
					proc_detail.setString(1, rptID);
					proc_detail.setString(2, row.get("rptfield").toString());	         
					proc_detail.setString(3, row.get("fieldname").toString());
					proc_detail.setString(4, row.get("datatype").toString());	
					proc_detail.setString(5, "" + iCountItem);
					proc_detail.setString(6, row.get("typeinout").toString());
					proc_detail.registerOutParameter(7, Types.VARCHAR);
					proc_detail.executeQuery();                   
					strResult = proc_detail.getString(7);
					iCountItem ++ ;
				}				
			}	
			
		} 
		catch (Exception e) 
		{
			strResult = "Error";
			e.printStackTrace();
		}
		finally 
		{
			ConnectionHelper.close(c);		
		}	
		return strResult ;
	}
	
	public String update_Report(String rptID,String rtpName, String rtpStatus, String rptFileName, String rptStoreName,
			String rptExport, String userAction,  Collection<ASObject> rptDetails) throws Exception
	{
		String strResult = "Error";  	
		int iCountItem = 0 ;
		Connection c = null;	
		try 
		{
			c = ConnectionHelper.getConnection();
			CallableStatement proc_stmt = null;
			proc_stmt = c.prepareCall("{ call report_update(?,?,?,?,?,?,?,?,?) }");
			proc_stmt.setString(1, rptID);
			proc_stmt.setString(2, rtpName);
			proc_stmt.setString(3, rtpName);
			proc_stmt.setString(4, rtpStatus);
			proc_stmt.setString(5, userAction);	
			proc_stmt.setString(6, rptFileName);
			proc_stmt.setString(7, rptStoreName);	
			proc_stmt.setString(8, rptExport);
			proc_stmt.registerOutParameter(9, Types.VARCHAR);
			proc_stmt.executeQuery();                   
			//rptID = proc_stmt.getString(8);
			strResult = proc_stmt.getString(9);
			
			
			if (rptDetails.size() > 0)
			{
				iCountItem = 1 ;
				for(ASObject row: rptDetails)
				{				
					CallableStatement proc_detail = null;
					proc_detail = c.prepareCall("{ call reportdetail_insert(?,?,?,?,?,?,?) }");
					proc_detail.setString(1, rptID);
					proc_detail.setString(2, row.get("rptfield").toString());	         
					proc_detail.setString(3, row.get("fieldname").toString());
					proc_detail.setString(4, row.get("datatype").toString());	
					proc_detail.setString(5, "" + iCountItem);
					proc_detail.setString(6, row.get("typeinout").toString());
					proc_detail.registerOutParameter(7, Types.VARCHAR);
					proc_detail.executeQuery();                   
					strResult = proc_detail.getString(7);
					iCountItem ++ ;				
				}				
			}	
			
		} 
		catch (Exception e) 
		{
			strResult = "Error";
			e.printStackTrace();
		}
		finally 
		{
			ConnectionHelper.close(c);		
		}	
		return strResult ;
	}
	
	public String delete_Report(String rptID, String userAction) throws Exception {
		String strResult = "Error";
		Connection c = null;		
		try
		{
			c = ConnectionHelper.getConnection();				
			
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call report_delete(?,?,?) }");
            
            proc_stmt.setString(1, rptID);
            proc_stmt.setString(2, userAction);
            proc_stmt.registerOutParameter(3, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(3);                
             																														
		} catch (SQLException e) {
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);		
		}		
		return strResult;
	}
	
	public List<Report> getReportList(String rptName) throws Exception{
		List<Report> list = new ArrayList<Report>();
		Connection c = null;
		try 
		{
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();	
			ResultSet rs = s.executeQuery("select * from report "			       	
			       	+ " where namerpt like '%" + rptName + "%'");
			while (rs.next()) {
				list.add(new Report(rs.getInt("idrpt"),
						rs.getString("namerpt"),
						rs.getString("description"),
						rs.getString("status"),
						rs.getDate("datecreate"),
						rs.getString("usercreate"),
						rs.getDate("datemodify"),
						rs.getString("usermodify"),
						rs.getString("rptfile"),
						rs.getString("storename"),
						rs.getString("exporttype")	
						)
						);								
			}		
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			list = null ;
		}
		finally 
		{
			ConnectionHelper.close(c);		
		}	
		return list;
	}
	
	public List<ReportDetail> getReportDetailList(String rptId) throws Exception{
		List<ReportDetail> list = new ArrayList<ReportDetail>();
		Connection c = null;
		try 
		{
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();	
			ResultSet rs = s.executeQuery("select * from reportdetail "			       	
			       	+ " where idrpt = '" + rptId + "'");
			while (rs.next()) {
				list.add(new ReportDetail(rs.getInt("idrpt"),
						rs.getString("rptfield"),
						rs.getString("fieldname"),
						rs.getString("datatype"),
						rs.getInt("fieldord"),
						rs.getString("typeinout")
						)
						);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			list = null ;
		}
		finally 
		{
			ConnectionHelper.close(c);		
		}	
		return list;
	}
}
